CREATE PROCEDURE dbo.asi_ConvertWorkInvoices
@batchKey uniqueidentifier,
@componentKey uniqueidentifier,
@userKey uniqueidentifier,
@systemKey uniqueidentifier
AS
DECLARE @wkInvKey uniqueidentifier
DECLARE @wkInvLineKey uniqueidentifier
DECLARE @invNum nvarchar(50)
DECLARE @payPriority int
DECLARE @unitPrice decimal(18,4)
DECLARE @lineQuantitySold decimal(18,4)
DECLARE @shipToAddrKey uniqueidentifier
DECLARE @shipToKey uniqueidentifier
DECLARE @invLineNum int
DECLARE @poNumber nvarchar(50)
DECLARE @currConversionRate decimal(18,4)
DECLARE @currCode nvarchar(3)
DECLARE @invSrcCodeKey uniqueidentifier
DECLARE @invLineSrcCodeKey uniqueidentifier
DECLARE @productKey uniqueidentifier
DECLARE @invLineSalesTeamGroupKey uniqueidentifier
DECLARE @basePrice decimal(18,4)
DECLARE @invLinePromoCode nvarchar(50)
DECLARE @shipMethodKey uniqueidentifier
DECLARE @invLinePriceSheetKey uniqueidentifier
DECLARE @invType nvarchar(1)
DECLARE @acctMethod nvarchar(10)
DECLARE @invDate datetime
DECLARE @pmtTermsKey uniqueidentifier
DECLARE @notes nvarchar(500)
DECLARE @orderKey uniqueidentifier
DECLARE @invFinEntityKey uniqueidentifier
DECLARE @billToKey uniqueidentifier
DECLARE @soldToKey uniqueidentifier
DECLARE @extendedPrice decimal(18,4)
DECLARE @parentWkInvLineKey uniqueidentifier
DECLARE @orderLineNumber int
DECLARE @orderNumber nvarchar(50)
DECLARE @extPriceHome decimal(18,4)
DECLARE @quantitySold decimal(18,4)
DECLARE @distFinEntityKey uniqueidentifier
DECLARE @uomKey uniqueidentifier
DECLARE @unitIncome decimal(18,4)
DECLARE @extIncome decimal(18,4)
DECLARE @extIncomeRec decimal(18,4)
DECLARE @incAcctKey uniqueidentifier
DECLARE @arAcctKey uniqueidentifier
DECLARE @defIncAcctKey uniqueidentifier
DECLARE @deferralTermsKey uniqueidentifier
DECLARE @parentInvLineNum int
DECLARE @newItemKey uniqueidentifier
DECLARE @newLineItemKey uniqueidentifier
DECLARE @orgKey uniqueidentifier
DECLARE @systemEntityKey uniqueidentifier
DECLARE @accessKey uniqueidentifier
DECLARE @createdByKey uniqueidentifier
DECLARE @createdOn datetime
DECLARE @updatedByKey uniqueidentifier
DECLARE @updatedOn datetime
DECLARE @commissionPlanKey uniqueidentifier
DECLARE @salesTeamGroupKey uniqueidentifier
DECLARE @ownerGroupKey uniqueidentifier
DECLARE @priceSheetKey uniqueidentifier
DECLARE @firstPaymentDueDate datetime
DECLARE @description nvarchar(50)
DECLARE @promoCode nvarchar(50)
DECLARE @extCost decimal(18,4)
DECLARE @invDistShipMethodKey uniqueidentifier
DECLARE @isPledge bit
DECLARE @lastWkInvKey uniqueidentifier
DECLARE @lastWkInvLineKey uniqueidentifier
DECLARE @nestedLinesCreated bit
DECLARE @origBatckKey uniqueidentifier
DECLARE @separateInvoiceStreamsParm nvarchar(1000)
DECLARE @invoiceCounterName nvarchar(20)
DECLARE @combinedCounterName nvarchar(20)
DECLARE @accrualCounterName nvarchar(20)
DECLARE @cashCounterName nvarchar(20)
DECLARE @separateInvoiceStreams bit
DECLARE @nestedLines TABLE (InvoiceLineKey uniqueidentifier, ParentLineNumber int)
DECLARE WorkInvoiceData CURSOR FAST_FORWARD FOR
SELECT wi.WorkInvoiceKey, wi.OrderNumber, wi.SoldToContactKey, wi.BillToContactKey, wi.FinancialEntityKey,
wi.Notes, wi.PaymentTermsKey, wi.InvoiceDate, wi.AccountingMethodCode, wi.InvoiceTypeCode,
wi.SourceCodeKey, wi.CurrencyCode, wi.CurrencyConversionRate, wi.PurchaseOrderNumber, wi.CommissionPlanKey,
wi.SalesTeamGroupKey, wi.OwnerGroupKey, wi.FirstPaymentDueDate, wi.Description, wi.PromoCode, wi.PriceSheetKey,
wil.WorkInvoiceLineKey, wil.InvoiceLineNumber, wil.ShipToContactKey, wil.ShipToFullAddressKey,
wil.QuantitySold, wil.UnitPrice, wil.PayPriority, wil.ExtendedPrice, wil.ParentWorkInvoiceLineKey,
wil.OrderLineNumber, wil.ExtendedPriceHome, wil.SourceCodeKey, wil.IsPledge, wil.UomKey, wil.ProductKey,
wil. SalesTeamGroupKey, wil.BasePrice, wil.PromoCode, wil.ShipMethodKey, wil.PriceSheetKey,
wid.QuantitySold, wid.FinancialEntityKey,
wid.UnitIncome, wid.ExtendedIncome, wid.ExtendedIncomeRecognized, wid.IncomeGLAccountKey, wid.ARGLAccountKey,
wid.DeferredIncomeGLAccountKey, wid.DeferralTermsKey, wid.ExtendedCost, wid.ShipMethodKey, wilParent.InvoiceLineNumber,
wi.SystemEntityKey, wi.AccessKey, wi.CreatedByUserKey, wi.CreatedOn, wi.UpdatedByUserKey, wi.UpdatedOn, wi.OriginatingBatchKey
FROM WorkInvoiceMain wi
INNER JOIN WorkInvoiceLine wil ON wil.WorkInvoiceKey = wi.WorkInvoiceKey
LEFT OUTER JOIN WorkInvoiceLine wilParent ON wilParent.ParentWorkInvoiceLineKey = wilParent.WorkInvoiceLineKey
LEFT OUTER JOIN WorkInvoiceDistribution wid ON wid.WorkInvoiceLineKey = wil.WorkInvoiceLineKey
WHERE wi.BatchKey = @batchKey
ORDER BY wi.WorkInvoiceKey, wil.WorkInvoiceLineKey
OPEN WorkInvoiceData
FETCH NEXT FROM WorkInvoiceData into @wkInvKey, @orderNumber, @soldToKey, @billToKey, @invFinEntityKey,
@notes, @pmtTermsKey, @invDate, @acctMethod, @invType ,@invSrcCodeKey ,@currCode ,@currConversionRate ,@poNumber,
@commissionPlanKey, @salesTeamGroupKey, @ownerGroupKey, @firstPaymentDueDate, @description, @promoCode, @priceSheetKey,
@wkInvLineKey, @invLineNum ,@shipToKey ,@shipToAddrKey ,@lineQuantitySold ,@unitPrice ,@payPriority ,@extendedPrice ,
@parentWkInvLineKey ,@orderLineNumber ,@extPriceHome , @invLineSrcCodeKey, @isPledge, @uomKey, @productKey,
@invLineSalesTeamGroupKey, @basePrice, @invLinePromoCode, @shipMethodKey, @invLinePriceSheetKey, @quantitySold,
@distFinEntityKey ,@unitIncome ,@extIncome ,@extIncomeRec ,@incAcctKey ,@arAcctKey ,@defIncAcctKey ,@deferralTermsKey,
@extCost, @invDistShipMethodKey, @parentInvLineNum, @systemEntityKey, @accessKey, @createdByKey, @createdOn,
@updatedByKey, @updatedOn, @origBatckKey
SET @nestedLinesCreated = 0
SET @combinedCounterName = 'Invoice'
SET @accrualCounterName = 'AccrualInvoice'
SET @cashCounterName = 'CashInvoice'
SELECT @separateInvoiceStreamsParm = ParameterValue from SystemConfig where ParameterName = 'Asi.Accounting.MaintainSeparateCashInvoiceSequence'
IF (@separateInvoiceStreamsParm is not null) AND (UPPER(@separateInvoiceStreamsParm) = 'TRUE')
SET @separateInvoiceStreams = '1'
ELSE
BEGIN
SET @separateInvoiceStreams = '0'
SET @invoiceCounterName = @combinedCounterName
END
WHILE @@FETCH_STATUS = 0
BEGIN
IF @lastWkInvKey is null OR @wkInvKey != @lastWkInvKey
BEGIN
SET @newItemKey = NEWID()
IF (@separateInvoiceStreams = '1')
BEGIN
IF @acctMethod = 'A'
SET @invoiceCounterName = 'AccrualInvoice'
ELSE
SET @invoiceCounterName = 'CashInvoice'
END
EXEC asi_NextSequenceValueOut @invNum OUT, @invoiceCounterName, @userKey, @systemKey
INSERT INTO UniformRegistry(UniformKey, ComponentKey)
VALUES (@newItemKey, @componentKey)
INSERT INTO InvoiceMain(InvoiceKey, InvoiceNumber, OrderNumber, SoldToContactKey, BillToContactKey,
FinancialEntityKey, Notes, PaymentTermsKey, InvoiceDate, AccountingMethodCode, InvoiceTypeCode,
SourceCodeKey, CurrencyCode, CurrencyConversionRate, PurchaseOrderNumber, CommissionPlanKey,
SalesTeamGroupKey, OwnerGroupKey, FirstPaymentDueDate, Description, PromoCode, PriceSheetKey,
FinalBatchKey, SystemEntityKey, AccessKey, CreatedByUserKey, CreatedOn, UpdatedByUserKey, UpdatedOn,
OriginatingBatchKey)
VALUES(@newItemKey, @invNum, @orderNumber, @soldToKey, @billToKey, @invFinEntityKey, @notes,
@pmtTermsKey, @invDate, @acctMethod, @invType, @invSrcCodeKey, @currCode, @currConversionRate,
@poNumber, @commissionPlanKey, @salesTeamGroupKey, @ownerGroupKey, @firstPaymentDueDate, @description,
@promoCode, @priceSheetKey, @batchKey, @systemEntityKey, @accessKey, @createdByKey, @createdOn,
@updatedByKey, @updatedOn, @origBatckKey)
END
IF @lastWkInvLineKey is null OR @wkInvLineKey != @lastWkInvLineKey
BEGIN
SET @newLineItemKey = NEWID()
INSERT INTO InvoiceLine(InvoiceLineKey, InvoiceKey, InvoiceNumber, InvoiceLineNumber, ShipToContactKey, ShipToFullAddressKey,
QuantitySold, UnitPrice, PayPriority, ExtendedPrice, ParentInvoiceLineKey, OrderLineNumber, ExtendedPriceHome, SourceCodeKey, IsPledge,
UomKey, ProductKey, SalesTeamGroupKey, BasePrice, PromoCode, ShipMethodKey, PriceSheetKey)
VALUES(@newLineItemKey, @newItemKey, @invNum, @invLineNum, @shipToKey, @shipToAddrKey,
@lineQuantitySold, @unitPrice, @payPriority, @extendedPrice, null, @orderLineNumber, @extPriceHome, @invLineSrcCodeKey, @isPledge, @uomKey,
@productKey, @invLineSalesTeamGroupKey, @basePrice, @invLinePromoCode, @shipMethodKey, @invLinePriceSheetKey)
IF(@parentWkInvLineKey is not null)
BEGIN
SET @nestedLinesCreated = 1
INSERT @nestedLines (InvoiceLineKey, ParentLineNumber) values (@newLineItemKey, @parentInvLineNum)
END
END
INSERT INTO InvoiceDistribution(InvoiceDistributionKey, InvoiceKey, InvoiceLineKey, QuantitySold, FinancialEntityKey,
UnitIncome, ExtendedIncome, ExtendedIncomeRecognized, IncomeGLAccountKey, ARGLAccountKey, DeferredIncomeGLAccountKey,
DeferralTermsKey, ExtendedCost, ShipMethodKey)
VALUES(NEWID(), @newItemKey, @newLineItemKey, @quantitySold, @distFinEntityKey, @unitIncome, @extIncome,
@extIncomeRec, @incAcctKey, @arAcctKey, @defIncAcctKey, @deferralTermsKey, @extCost, @invDistShipMethodKey)
SET @lastWkInvKey = @wkInvKey
SET @lastWkInvLineKey = @wkInvLineKey
FETCH NEXT FROM WorkInvoiceData into @wkInvKey, @orderNumber, @soldToKey, @billToKey, @invFinEntityKey,
@notes, @pmtTermsKey, @invDate, @acctMethod, @invType ,@invSrcCodeKey ,@currCode ,@currConversionRate ,@poNumber,
@commissionPlanKey, @salesTeamGroupKey, @ownerGroupKey, @firstPaymentDueDate, @description, @promoCode, @priceSheetKey,
@wkInvLineKey, @invLineNum ,@shipToKey ,@shipToAddrKey ,@lineQuantitySold ,@unitPrice ,@payPriority,
@extendedPrice ,@parentWkInvLineKey ,@orderLineNumber ,@extPriceHome , @invLineSrcCodeKey, @isPledge, @uomKey,
@productKey, @invLineSalesTeamGroupKey, @basePrice, @invLinePromoCode, @shipMethodKey, @invLinePriceSheetKey,
@quantitySold, @distFinEntityKey ,@unitIncome ,@extIncome ,@extIncomeRec ,@incAcctKey ,@arAcctKey ,@defIncAcctKey,
@deferralTermsKey, @extCost, @invDistShipMethodKey, @parentInvLineNum, @systemEntityKey, @accessKey, @createdByKey,
@createdOn, @updatedByKey, @updatedOn, @origBatckKey
END
CLOSE WorkInvoiceData
DEALLOCATE WorkInvoiceData
IF @nestedLinesCreated = 1
BEGIN
UPDATE il SET ParentInvoiceLineKey = ilParent.InvoiceLineKey
FROM InvoiceLine il
INNER JOIN @nestedLines nl ON nl.InvoiceLineKey = il.InvoiceLineKey
INNER JOIN InvoiceLine ilParent ON nl.ParentLineNumber = ilParent.InvoiceLineNumber
END
GO
[dbo].[asi_ConvertWorkInvoices]